package com.pojo;

import cn.hutool.core.bean.BeanUtil;
import cn.hutool.core.collection.ListUtil;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.date.TimeInterval;
import cn.hutool.core.io.FileUtil;
import cn.hutool.core.io.LineHandler;
import cn.hutool.core.io.file.FileMode;
import cn.hutool.core.lang.Opt;
import cn.hutool.core.map.MapUtil;
import cn.hutool.core.util.IdUtil;
import cn.hutool.core.util.NumberUtil;
import cn.hutool.core.util.ReflectUtil;
import cn.hutool.core.util.StrUtil;
import com.Util;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.pojo.zhanzhi.InspectCleanPrimaryKeyDto;
import com.test.POS;
import com.test.TEMP;
import com.util.excel.*;
import com.util.gis.GisUtil;
import com.util.other.AvgUtil;
import javafx.geometry.Pos;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.docx4j.wml.P;

import java.io.*;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.nio.charset.Charset;
import java.util.*;
import java.util.List;
import java.util.concurrent.atomic.AtomicBoolean;
import java.util.function.Consumer;
import java.util.stream.Collectors;


/**
 * @Description: TODO
 * @Author: hechaobo
 * @Date: 2023/10/13
 **/
@Slf4j
public class Pojo {


    //打印表头对应实体类
    public static void printFiled() {
        FileUtilH.read("D:\\temp\\tt.xlsx", new Listener() {
            @Override
            public void invoke(Map<Integer, String> integerStringMap, AnalysisContext analysisContext) {
                super.invoke(integerStringMap, analysisContext);
                for (String value : integerStringMap.values()) {
                    System.out.println("    @ExcelProperty(\"" + value + "\")");
                    System.out.println("String " + value + ";");
                }
            }
        });
    }

    public static void printHead() {
        List<List> list = new LinkedList();
        EasyExcel.read("D:\\temp\\t.xlsx", new Listener() {
            @Override
            public void invoke(Map<Integer, String> integerStringMap, AnalysisContext analysisContext) {
                super.invoke(integerStringMap, analysisContext);
                list.add(ListUtil.toList(integerStringMap.values()));
            }
        }).sheet().headRowNumber(0).doRead();
        int size = list.get(0).size();
        System.out.println("        List<List<String>> head=new LinkedList<>();\n");
        for (int i = 0; i < size; i++) {
            System.out.println("head.add(ListUtil.toList(");

            List t = new LinkedList();
            for (int i1 = 0; i1 < list.size(); i1++) {
                Object o = list.get(i1).get(i);
                String s;
                if (o != null) {
                    s = (String) o;
                } else s = "";
                t.add("\"" + s + "\"");
            }
            System.out.println(t.stream().collect(Collectors.joining(",")));
            System.out.println("));\n");
        }
        System.out.println("        return head;\n");
    }



    //提取网管
    public static void extractNetworkManagement() {
        FileUtil.walkFiles(new File("D:\\网管\\网管0619.rar等13个文件"), f -> {
            FileUtil.move(f, new File("D:\\temp\\网管\\6月\\" + f.getName()), false);
        });

    }

    public static void main(String[] args) throws Exception {
       determineLongitudeAndLatitude();
    }

    private static void extracted() throws IOException {
        List<String> paths = new LinkedList<>();
        FileUtilH.read("/temp/t.xlsx", new Listener() {
            @Override
            public void invoke(Map<Integer, String> integerStringMap, AnalysisContext analysisContext) {
                super.invoke(integerStringMap, analysisContext);
                paths.add(integerStringMap.get(0));
            }
        });

        Set<String> ips = new HashSet<>();
        FileUtilH.read("/temp/告警次数.csv", new Listener() {
            @Override
            public void invoke(Map<Integer, String> integerStringMap, AnalysisContext analysisContext) {
                ips.add(integerStringMap.get(0));
            }
        });
        BufferedWriter writer = FileUtil.getWriter("/temp/ip2radius.csv", Charset.forName("utf-8"), false);
        for (String path : paths) {
            log.info(path);

            BufferedReader reader = FileUtil.getReader(path, Charset.forName("utf-8"));
            String line = null;
            while ((line=reader.readLine())!=null){
                String[] split = line.split("\\|");
                try {
                    String ip = split[15].split(" ")[2].split("/")[0];
                    if (ips.contains(ip)) {
                        writer.write(line);
                        writer.newLine();
                    }
                } catch (Exception e) {

                }
            }
            reader.close();
        }
        writer.close();
    }


    public static void cutOver() {
        List<String> paths = new LinkedList<>();
        FileUtilH.read("/temp/t.xlsx", new Listener() {
            @Override
            public void invoke(Map<Integer, String> integerStringMap, AnalysisContext analysisContext) {
                super.invoke(integerStringMap, analysisContext);
                paths.add(integerStringMap.get(0));
            }
        });
        Map<String, Radius> map = new HashMap<>();
        for (String path : paths) {
            log.info(path);
            FileUtilH.readCsv(path, '|', Radius.class, r -> {
                try {
                    String[] split1 = r.getVlanInfo().split(" ")[2].split("/");
                    r.setIpPon(split1[0] + "-" + split1[2] + "-" + split1[3]);
                    r.setOntId(split1[4]);
                } catch (Exception e) {
                    return;
                }
                String key = r.getAccount() + r.getIpPon();
                if (!map.containsKey(key)) {
                    map.put(key, r);
                }
            });

        }
        WriterH writerH = new WriterH("/temp/radius.csv");
        Map<String, List<Radius>> collect = map.values().stream().collect(Collectors.groupingBy(Radius::getAccount));
        collect.values().stream().filter(l -> l.size() > 1).forEach(l -> {
            l.forEach(value -> {
                writerH.write(value.getAccount(), value.getOnlineTime(), value.getOfflineTime(), value.getIpPon(), value.getOntId());
            });
        });
        writerH.close();
    }

    public static List<List<String>> getCellHead() {
        List<List<String>> head = new LinkedList<>();

        head.add(ListUtil.toList("数据来源", "分公司", "字段说明", "权重（可配置）", "基准值", "挑战值"));

        head.add(ListUtil.toList("综资-小区自然村配置查询结果", "小区名称", "综资的小区、自然村配置查询结果，去掉删除字样小区", "--", "--", "--"));

        head.add(ListUtil.toList("综资-小区自然村配置查询结果", "小区ID", "从小区_学校_自然村的配置查询结果中“网元内部编码”中获取小区ID", "--", "--", "--"));

        head.add(ListUtil.toList("综资-小区自然村配置查询结果", "可营销标识", "用小区ID匹配可营销小区报表中“小区ID”字段，能匹配到的标识为“可营销”，否则为“不可营销”", "--", "--", "--"));

        head.add(ListUtil.toList("两区小区清单", "小区标识", "当前小区是否具有重保小区、千兆标杆小区标识", "--", "--", "--"));

        head.add(ListUtil.toList("BI小区清单", "小区规模", "小区按到达用户数：大>1000，中200-1000，小<200。", "--", "--", "--"));

        head.add(ListUtil.toList("OLT业务配置表", "网元名称", "OLT业务配置表中网元名称字段", "--", "--", "--"));

        head.add(ListUtil.toList("oltIp", "oltIp", "OLT业务配置表中管理IP字段", "--", "--", "--"));

        head.add(ListUtil.toList("用户对应小区关系的基表", "小区总用户数", "统计用户对应小区基表的用户数，去除企宽用户，定期更新移机信息", "--", "--", "--"));

        head.add(ListUtil.toList("BI小区清单", "活跃用户数", "BI小区清单里活跃用户数字段", "--", "--", "--"));

        head.add(ListUtil.toList("BI小区清单", "到达用户数", "BI小区清单里到达用户数字段", "--", "--", "--"));

        head.add(ListUtil.toList("BI小区清单", "覆盖用户数", "BI小区清单里覆盖用户数字段", "--", "--", "--"));

        head.add(ListUtil.toList("BI小区清单", "渗透率", "到达用户数/覆盖用户数", "--", "--", "--"));

        head.add(ListUtil.toList("北京网管", "网管在线用户数（个）", "统计网管SN号并去重", "--", "--", "--"));

        head.add(ListUtil.toList("pppoe", "pppoe近30天内上线用户数（个）", "近30天PPPOE有拨号记录的宽带账号匹配基表得到小区信息", "--", "--", "--"));

        head.add(ListUtil.toList("pppoe", "pppoe三个月内上线用户数（个）", "近90天有拨号记录的宽带账号匹配基表得到小区信息", "--", "--", "--"));

        head.add(ListUtil.toList("分公司匹配", "pon口总数（个）", "根据pon口去重", "--", "--", "--"));

        head.add(ListUtil.toList("超限pon", "用户超限pon口数（个）", "单pon口用户数大于64", "--", "--", "--"));

        head.add(ListUtil.toList("北京网管", "超限pon口占比（%）", "超限pon口数/pon口总数", "5%", "0%", "0%"));

        head.add(ListUtil.toList("北京网管", "弱光用户数（个）", "统计近30天网管中平均接收光功率小于-27记录数", "--", "--", "--"));

        head.add(ListUtil.toList("北京网管", "弱光率（%）", "公式：弱光用户数/小区总用户数", "5%", "0.8%", "0.5%"));

        head.add(ListUtil.toList("北京网管", "异常弱光用户数（个）", "光功率<-31db的天数超过6天（一个月20%），的用户累计", "--", "--", "--"));

        head.add(ListUtil.toList("北京网管", "异常弱光率（%）", "异常弱光用户数/弱光用户数", "5%", "10%", "5%"));

        head.add(ListUtil.toList("品宽光路劣化", "光路劣化pon口数（个）", "集中弱光PON口和质差分光器所在PON口总数", "--", "--", "--"));

        head.add(ListUtil.toList("品宽光路劣化", "劣化pon口占比", "劣化pon口数/pon口数", "10%", "0%", "0%"));

        head.add(ListUtil.toList("radius、告警", "radius关联告警掉线次数（人次）", "统计RADIUS掉线关联到告警的人次，去掉1-6点的，掉电也去掉", "--", "--", "--"));

        head.add(ListUtil.toList("radius、告警", "radius关联告警掉线用户数（个）", "对掉线人次去重", "--", "--", "--"));

        head.add(ListUtil.toList("radius、告警", "radius关联告警掉线率（%）", "掉线用户数/小区总用户数", "15%", "0.5%(全市60%)", "0.0%(全市15%)"));

        head.add(ListUtil.toList("radius、告警", "radius重复告警（单用户重复掉线）", "单用户掉线关联到告警的次数大于1", "15%", "0", "0"));

        head.add(ListUtil.toList("radius、告警", "radius多次告警（多次PON口及以上告警个数）", "pon口以上级别告警次数>1", "15%", "0", "0"));

        head.add(ListUtil.toList("radius、告警", "radius匹配告警中断时长(H)（radius关联告警修复时长）", "告警时长/小区总用户数", "15%", "4", "2"));

        head.add(ListUtil.toList("品宽光功率波动明细表", "光功率波动次数（人次）", "统计光功率最大值-最小值相差3MDB以上的记录数之和", "--", "--", "--"));

        head.add(ListUtil.toList("品宽光功率波动明细表", "光功率波动用户数（个）", "光功率波动人次去重", "--", "--", "--"));

        head.add(ListUtil.toList("品宽光功率波动明细表", "波动率（%）", "波动用户数/小区总用户数", "5%", "6.0%(全市60%)", "1.0%(全市15%)"));

        head.add(ListUtil.toList("OLT板卡高温", "OLT板卡温度（℃）", "仅显示温度异常高温，多块板卡高温，取最大值", "10%", "64", "64"));

        head.add(ListUtil.toList("综合得分", "健康度评分（满分100）", "(根据每项指标挑战值标准值线性得分求和)", "--", "--", "85"));

        return head;
    }

    private static int getWeakCoverDistance(String scenes) {
        if (scenes.contains("农村")) {
            return 800;
        } else if (scenes.equals("一般城区") || scenes.equals("县城")) {
            return 700;
        } else {
            return 400;
        }
    }
    private static int getNoCoverDistance(String scenes){
        if(scenes.contains("农村")){
            return 1200;
        }else if(scenes.equals("一般城区")||scenes.equals("县城")){
            return 900;
        }
        else {
            return 600;
        }
    }

    public static void determineLongitudeAndLatitude() {
        List<TEMP> list = new LinkedList<>();//所有基站
        List<TEMP> re = new LinkedList<>();//所有储备基站
        int distance = 30000;//编码间距
        EasyExcel.read("D:\\temp\\nb\\reservestation\\reservestation.csv", new Listener() {
            @Override
            public void invoke(Map<Integer, String> integerStringMap, AnalysisContext analysisContext) {
                super.invoke(integerStringMap, analysisContext);
                String lon = getByColName("longitude");
                String lat = getByColName("latitude");
                TEMP temp = new TEMP();
                temp.setName(getByColName("nodeb_name"));
                temp.setScenes(getByColName("cover_scenes"));
                temp.setLon(Double.parseDouble(lon));
                temp.setLat(Double.parseDouble(lat));
                double[] doubles = GisUtil.meridianToMercator(temp.getLon(), temp.getLat());
                temp.setCode(GisUtil.getCode(doubles[0], doubles[1], distance));
                re.add(temp);
            }
        }).sheet().doRead();
        EasyExcel.read("D:\\测试文件\\parameter2024-01.xlsx", new Listener() {
            @Override
            public void invoke(Map<Integer, String> integerStringMap, AnalysisContext analysisContext) {
                super.invoke(integerStringMap, analysisContext);
                String lon = getByColName("经度");
                String lat = getByColName("纬度");
                TEMP temp = new TEMP();
                temp.setName(getByColName("基站名称"));
                temp.setScenes(getByColName("覆盖场景"));
                temp.setLon(Double.parseDouble(lon));
                temp.setLat(Double.parseDouble(lat));
                double[] doubles = GisUtil.meridianToMercator(temp.getLon(), temp.getLat());
                temp.setCode(GisUtil.getCode(doubles[0], doubles[1], distance));
                list.add(temp);
            }
        }).sheet().headRowNumber(3).doRead();
        EasyExcel.read("D:\\测试文件\\parameter2024-01.xlsx", new Listener() {
            @Override
            public void invoke(Map<Integer, String> integerStringMap, AnalysisContext analysisContext) {
                super.invoke(integerStringMap, analysisContext);
                String lon = getByColName("经度");
                String lat = getByColName("纬度");
                TEMP temp = new TEMP();
                temp.setName(getByColName("基站名称"));
                temp.setScenes(getByColName("覆盖场景"));
                temp.setLon(Double.parseDouble(lon));
                temp.setLat(Double.parseDouble(lat));
                double[] doubles = GisUtil.meridianToMercator(temp.getLon(), temp.getLat());
                temp.setCode(GisUtil.getCode(doubles[0], doubles[1], distance));
                list.add(temp);
            }
        }).sheet(1).headRowNumber(3).doRead();
        Map<Long, List<TEMP>> map = list.stream().collect(Collectors.groupingBy(TEMP::getCode));
        Map<Long, List<TEMP>> remap = re.stream().collect(Collectors.groupingBy(TEMP::getCode));

        List<List> data = new LinkedList<>();
        EasyExcel.read("D:\\测试文件\\第20批-重点业务区域NB网络需求清单-河北邯郸-1205-266个点位（仅查询NB和4G信号，反馈结果即可）.xlsx", new Listener() {
            @Override
            public void invoke(Map<Integer, String> integerStringMap, AnalysisContext analysisContext) {
                super.invoke(integerStringMap, analysisContext);
                String s = getByColName("*落地具体坐标（小数点后保留5位）");
                if (StrUtil.isEmpty(s)) {
                    data.add(new LinkedList());
                    return;
                }
                String[] split = s.split("，");
                if(split.length==1){
                    split=s.split(",");
                }
                double lon = Double.parseDouble(split[1]);
                double lat = Double.parseDouble(split[0]);
                List<Long> codeNearMeridian = GisUtil.getCodeNearMeridian(lon, lat, distance);
                TEMP res = null;
                TEMP reres = null;
                double min = Integer.MAX_VALUE;
                double remin = Integer.MAX_VALUE;
                for (Long aLong : codeNearMeridian) {
                    List<TEMP> temps = map.get(aLong);
                    if (temps == null) continue;
                    for (TEMP temp : temps) {
                        double v = GisUtil.calculateDistance(temp.getLon(), temp.getLat(), lon, lat);
                        if (v < min) {
                            min = v;
                            res = temp;
                        }
                    }
                }
                 for (Long aLong : codeNearMeridian) {
                    List<TEMP> temps = remap.get(aLong);
                    if (temps == null) continue;
                    for (TEMP temp : temps) {
                        double v = GisUtil.calculateDistance(temp.getLon(), temp.getLat(), lon, lat);
                        if (v < remin) {
                            remin = v;
                            reres = temp;
                        }
                    }
                }
                String isCover = "否";
                String name = "";
                String scenes = "";

                //                    没有正式
                if (res == null||getNoCoverDistance(res.getScenes())<min) {
                    if(reres==null||getNoCoverDistance(reres.getScenes()==null?"":reres.getScenes())<remin){
                        isCover="无NB覆盖，附近无储备站";
                    }else {
                        isCover="无NB覆盖，附近有储备站";
                    }
                }else {
                    name=res.getName();
                    scenes=res.getScenes();
                    if(getWeakCoverDistance(scenes)<min){
                        isCover="有覆盖，当前位置疑似弱覆盖";
                    }else {
                        isCover="有覆盖，且NB覆盖情况良好";
                    }
                }
                data.add(ListUtil.toList(isCover,scenes,min,remin));
            }

            @Override
            public void onException(Exception exception, AnalysisContext context) throws Exception {
                System.out.println(1);
            }
        }).sheet().doRead();
        EasyExcel.write("/temp/t.xlsx").sheet().doWrite(data);
    }


    /**
     * 计时
     *
     * @param runnable
     */
    public static void timing(Runnable runnable) {
        TimeInterval interval = new TimeInterval();
        interval.start();
        runnable.run();
        System.out.println(interval.interval());
    }

}
